Re: [GENERAL] problems with a sub-select (takes donkeys' years) - Mailing list pgsql-general

From Herouth Maoz
Subject Re: [GENERAL] problems with a sub-select (takes donkeys' years)
Date
Msg-id l03110707b3255fa371eb@[147.233.148.145]
Whole thread Raw
In response to problems with a sub-select (takes donkeys' years)  (Stuart Rison <stuart@ludwig.ucl.ac.uk>)
List pgsql-general
At 18:32 +0200 on 29/03/1999, Stuart Rison wrote:


> Can anyone explain why the sub-query form takes so long?

Basically, when you do the separation yourself, it means you executed the
sub query once, and then use it as a constant for another query. When you
combine them together, the internal select is ran over and over again,
because there is no way for the optimiser to know that the result will be
the same in all runs...

How about trying a different approach, such as:

SELECT brecard_id,count(brecard_id)
FROM malignant_pathologies p1
WHERE 3 = (
    SELECT count(*)
    FROM malignant_pathologies p2
    WHERE p2.brecard_id = p1.brecard_id
      AND code in ( 'MAPH', 'AMCA', 'LOCA' )
);

This may need tweaking, you are the one who knows distinctness. One of the
troubles of Postgres's SQL92 compatibility is its lack of support for
SELECT COUNT( DISTINCT code ) which is necessary if the combination of
brecard_id and code is not unique. Also, I'm not sure it allows constructs
such as WHERE (brecard_id,3) = ( SELECT brecard_id, count(*) FROM ....).
Gurus?

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma



pgsql-general by date:

Previous
From: Stuart Rison
Date:
Subject: problems with a sub-select (takes donkeys' years)
Next
From: Herouth Maoz
Date:
Subject: Re: [GENERAL] problems with a sub-select (takes donkeys' years)